package com.ht.dao;

import com.ht.bean.*;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.List;
import java.util.Map;


public interface UserDao {

    //学生表------------------------------------------------------------------------------student
    @Select("select * from student")
    List<Student> stulist();

    @Select("select * from studentclass")
    List<Studentclass> sclist();

    @Select("select * from studenthuor")
    List<Studenthuor> shlist();
    @Select("select * from major")
    List<Major> mlist();

    //查询学生表（后续加模糊查询）
    @Select("<script>" +
            "select sc.className,sh.huorName ,s.* from student s,studenthuor sh,studentclass sc " +
            "<where>" +
            " s.huor = sh.huorId and s.clazz = sc.classId " +
            "<if test=\"st.stuId!=null and st.stuId!=''\">" +
            " and stuId=#{st.stuId} " +
            "</if>" +
            "<if test=\"st.stuname!=null and st.stuname!=''\">" +
            " and stuname like concat('%',#{st.stuname},'%') " +
            "</if>" +
            "</where>" +
            "limit #{startIndex},#{pageSize}" +
            "</script>")
//    @Select("select * from student")
    List<Map> allstu(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize, @Param("st") Student student);

    //查询学生表总行数
    @Select("<script>" +
            "select count(*) from student s,studenthuor sh,studentclass sc " +
            "<where>" +
            " s.huor = sh.huorId and s.clazz = sc.classId " +
            "<if test=\"st.stuId!=null and st.stuId!='' \">" +
            " and stuId=#{st.stuId} " +
            "</if>" +
            "<if test=\"st.stuname!=null and st.stuname!='' \">" +
            " and stuname like concat('%',#{st.stuname},'%') " +
            "</if>" +
            "</where>" +
            "</script>")
    int countStudent(@Param("st") Student student);

    /**
     * 删除学生
     *
     * @param
     * @return
     */
    @Delete("delete from student where stuId=#{stuId}")
    boolean delstu(Integer stuId);

    /**
     * 新增学生
     *
     * @param
     * @return
     */
//    @Insert("insert into student(stuId,password,stuname,middleschool) values(null, '123456', #{stuname}, #{middleschool})")
//     boolean insestu(Student student);
    @Insert("insert into student values(#{stuId}, '123456', #{stuname}, #{middleschool}, #{sex}, #{age}, #{birthday}, #{phone}, #{addr}, #{clazz}, #{huor}, #{entertime}, #{introduretech}, #{stat}, #{nation}, #{natives}, #{residence}, #{cardid}, #{professional}, #{prolevel}, #{studytype}, #{parents}, #{parentsphone}, #{intrphone}, #{audition}, #{auditionoption}, #{isvocational}, #{vocationalsch}, #{vocationalflag}, #{enrollno}, #{qkMoney}, #{score}, #{dibao}, #{sourceType}, #{guarantee}, #{soldier}, #{registration}, #{zhuxiao}, #{remark}, #{tuixue}, #{xiuxue}, #{stuno}, #{computer}, #{collar}, #{grants}, #{headImg}, #{rollId})")
    boolean insestu(Student student);

    //根据id查学生
    @Select("select * from student where stuId=#{stuId}")
    Student allstubyid(Integer stuId);

    //修改学生信息
    @Update("update student set password=#{password},stuname=#{stuname},middleschool=#{middleschool},sex=#{sex},age=#{age},birthday=#{birthday},phone=#{phone} , addr=#{addr} , clazz=#{clazz} , huor=#{huor} , entertime=#{entertime} , introduretech=#{introduretech} , stat=#{stat} , nation=#{nation} , natives=#{natives} , residence=#{residence} , cardid=#{cardid} , professional=#{professional} , prolevel=#{prolevel} , studytype=#{studytype} , parents=#{parents} , parentsphone=#{parentsphone} , intrphone=#{intrphone} , audition=#{audition} , auditionoption=#{auditionoption} , isvocational=#{isvocational} , vocationalsch=#{vocationalsch} , vocationalflag=#{vocationalflag} , enrollno=#{enrollno} , qkMoney=#{qkMoney} , score=#{score} , dibao=#{dibao} , sourceType=#{sourceType} , guarantee=#{guarantee} , soldier=#{soldier} , registration=#{registration} , zhuxiao=#{zhuxiao} , remark=#{remark} , tuixue=#{tuixue} , xiuxue=#{xiuxue} , stuno=#{stuno} , computer=#{computer} , collar=#{collar} , grants=#{grants} , headImg=#{headImg} , rollId=#{rollId} where stuId=#{stuId}")
    boolean updstu(Student student);


    //管理班级查询-------------------------------------------------------------------------------------------------------------------

    @Select("<script>" +
            "select stu.classId ,stu.classno,stu.className,stu.count,e.empName,e2.empName empName1,cl.classTypeName,stu.remark,sf.level,dt.deptName,ma.majorName from studentclass stu ,classtype cl ,studentfall sf ,dept dt ,major ma,emp e,emp e2" +//,e.empname     ,emp e   ,classteacher ct
            "<where>" +
            "and stu.classType = cl.calssTypeId and stu.fallId=sf.fallId and stu.deptId=dt.deptId and stu.majorId=ma.majorId and stu.teacher=e.empid and stu.classTeacher=e2.empId " +// and e.empId=ct.empId
            "<if test=\"st.classId!=null and st.classId!=''\">" +
            "and stu.classId=#{st.classId}" +
            "</if>" +
            "<if test=\"st.className!=null and st.className!=''\">" +
            "and stu.className like concat('%',#{st.className},'%')" +
            "</if>" +
            "</where>" +
            " limit #{startIndex},#{pageSize}" +
            "</script>")
    List<Map> classlist(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize, @Param("st") Studentclass Studentclass);

    /**
     * 查询班级表总行数
     *
     * @param
     * @return
     */
    @Select("<script>" +
            "select count(*) from studentclass stu ,classtype cl ,studentfall sf ,dept dt ,major ma,emp e,emp e2 " +
            "<where>" +
            "and stu.classType = cl.calssTypeId and stu.fallId=sf.fallId and stu.deptId=dt.deptId and stu.majorId=ma.majorId and stu.teacher=e.empid and stu.classTeacher=e2.empId " +
            "<if test=\"st.classId!=null and st.classId!=''\">" +
            "and classId=#{st.classId}" +
            "</if>" +
            "<if test=\"st.className!=null and st.className!=''\">" +
            "and className like concat('%',#{st.className},'%')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int countclass(@Param("st") Studentclass Studentclass);

    //查询班级类型表
    @Select("select * from classtype")
    List<Classtype> ctypelist();

    //查询届别表
    @Select("select * from studentfall")
    List<Studentfall> sfalllist();

    //查询系别表
    @Select("select * from dept")
    List<Dept> deptlist();

    //查询专业表
    @Select("select * from major")
    List<Major> majorlist();

    //查询员工表
    @Select("select * from emp")
    List<Emp> emplist();

    //查询班级老师表
    @Select("select * from classteacher")
    List<Classteacher> ctlist();

    //查询学期表
    @Select("select * from term")
    List<Term> tlist();

    @Select("select * from course")
    List<Course> clist();

    //管理班级删除
    @Delete("delete from studentclass where classId=#{classId}")
    public boolean classdel(String classId);

    //管理班级新增
    @Insert("insert into studentclass values(null,#{classno},#{className},#{count},#{teacher},#{classTeacher},#{classType},#{remark},#{fallId},#{deptId},#{majorId})")
    public boolean classadd(Studentclass studentclass);

    //班级管理根据id查找对象
    @Select("select * from studentclass where classId=#{classId}")
    public Studentclass classbyid(String classId);

    //管理班级修改
    @Update("update studentclass set classno=#{classno},className=#{className},count=#{count},teacher=#{teacher},classTeacher=#{classTeacher},classType=#{classType},remark=#{remark},fallId=#{fallId},deptId=#{deptId},majorId=#{majorId} where classId=#{classId}")
    public boolean classupd(Studentclass studentclass);

    //查询成绩表
    @Select("<script>" +
            "select ss.scoreId,stu.stuname,t.termName,c.courseName,ss.score,ss.rescore,ss.testType,ss.scoreTime,e.empName,ss.remark from studentscore ss, student stu, course c,term t,emp e " +
            "<where>" +
            "ss.stuId=stu.stuId and ss.courseId=c.courseId and ss.termId=t.termId and ss.empId=e.empId " +
            "<if test=\" c.courseName!='' and c.courseName!=null \">" +//课程名
            " and c.courseName like concat('%',#{c.courseName},'%')" +
            "</if>" +
            "<if test=\" stu.stuname!='' and stu.stuname!=null \">" +//学生名字
            " and stu.stuname like concat('%',#{stu.stuname},'%')" +
            "</if>" +
            "</where>" +
            " limit #{startIndex},#{pageSize}" +
            "</script>")
    public List<Map> stuscorelist(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize, @Param("stu") Student student, @Param("c") Course course);//@Param("ss") Studentscore studentscore

    //查询成绩表总行数
    @Select("<script>" +
            "select count(*) from studentscore ss, student stu, course c,term t,emp e " +
            "<where>" +
            "ss.stuId=stu.stuId and ss.courseId=c.courseId and ss.termId=t.termId and ss.empId=e.empId " +
            "<if test=\" c.courseName!='' and c.courseName!=null \">" +//课程名
            " and c.courseName like concat('%',#{c.courseName},'%')" +
            "</if>" +
            "<if test=\" stu.stuname!='' and stu.stuname!=null \">" +//学生名字
            " and stu.stuname like concat('%',#{stu.stuname},'%')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int countstuscore(@Param("stu") Student student, @Param("c") Course course);

    //添加学生成绩
    @Insert("insert into studentscore values(#{scoreId},#{stuId},#{score},#{rescore},#{courseId},#{testType},#{termId},#{scoreTime},#{empId},#{remark},#{testName})")
    public boolean addstuscore(Studentscore studentscore);

    //删除学生成绩
    @Delete("delete from studentscore where scoreId=#{scoreId}")
    public boolean delstuscore(String scoreId);

    //根据id查学生成绩
    @Select("select * from studentscore where scoreId=#{scoreId}")
    public Studentscore stuscorebyid(String scoreId);

    //修改学生
    @Update("update studentscore set stuId=#{stuId},score=#{score},rescore=#{rescore},courseId=#{courseId},testType=#{testType},termId=#{termId},scoreTime=#{scoreTime},empId=#{empId},remark=#{remark},testName=123456 where scoreId=#{scoreId}")
    public boolean updstuscore(Studentscore studentscore);

    //-----------------------以下是答辩成绩管理----------------------------------------------------------------------------

    //查询答辩成绩
    @Select("<script>" +
            "select srs.replyId,stu.stuname,pj.projectName,e.empName,srs.score1,srs.score2,srs.score3,srs.score4,srs.score5,srs.score6,srs.score7,srs.remark\n" +
            "from studentreplyscore srs,student stu,project pj,emp e " +
            "<where>" +
            "srs.studentId=stu.stuId and srs.projectId=pj.projectId and srs.empId=e.empId " +
            "<if test=\"stu.stuname!='' and stu.stuname!=null\">" +
            " and stu.stuname like concat('%',#{stu.stuname},'%') " +
            "</if>" +
            "<if test=\"pj.projectName!='' and pj.projectName!=null\">" +
            " and pj.projectName like concat('%',#{pj.projectName},'%') " +
            "</if>" +
            "</where>" +
            " limit #{startIndex},#{pageSize}" +
            "</script>")
    public List<Map> stureplyscore(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize, @Param("stu") Student student, @Param("pj") Project project);

    //查询总行数
    @Select("<script>" +
            "select count(*) from studentreplyscore srs,student stu,project pj,emp e" +
            "<where>" +
            "srs.studentId=stu.stuId and srs.projectId=pj.projectId and srs.empId=e.empId " +
            "<if test=\"stu.stuname!='' and stu.stuname!=null\">" +
            " and stu.stuname like concat('%',#{stu.stuname},'%')" +
            "</if>" +
            "<if test=\"pj.projectName!='' and pj.projectName!=null\">" +
            " and pj.projectName like concat('%',#{pj.projectName},'%')" +
            "</if>" +
            "</where>" +
            "</script>")
    public int countstureplyscore(@Param("stu") Student student, @Param("pj") Project project);

    //添加答辩成绩
    @Insert("insert into Studentreplyscore values(null,#{studentId},#{projectId},#{empId},#{score1},#{score2},#{score3},#{score4},#{score5},#{score6},#{score7},#{remark})")
    public boolean addstureplyscore(Studentreplyscore studentreplyscore);

    //查询项目表
    @Select("select * from project")
    public List<Project> prolist();

    //删除答辩成绩
    @Delete("delete from studentreplyscore where replyId=#{replyId}")
    public boolean delstureplyscore(String replyId);

    //根据id查答辩成绩
    @Select("select * from studentreplyscore where replyId=#{replyId}")
    public Studentreplyscore stursbyid(String replyId);

    //修改答辩成绩
    @Update("update studentreplyscore set studentId=#{studentId},projectId=#{projectId},empId=#{empId},score1=#{score1},score2=#{score2},score3=#{score3},score4=#{score4},score5=#{score5},score6=#{score6},score7=#{score7},remark=#{remark} where replyId=#{replyId}")
    public boolean updstureplyscore(Studentreplyscore studentreplyscore);


    //班级分配--------------------------------------------------------------------------------------
    @Select("select c.classId,c.className,c.fallId from studentclass c")
    public List<Studentclass> classlistt();

    //届别表
    @Select("select s.fallId,s.level from studentfall s")
    public List<Studentfall> falllistt();

    //查询班级信息
    @Select("select st.stuname,st.sex,st.phone,sc.className from studentclass sc,student st where st.clazz=sc.classId and st.clazz=#{classId} limit #{startIndex},#{pageSize}")
    public List<Map> yfblist(@Param("classId")String classId,@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
    @Select("select count(*) from studentclass sc,student st where st.clazz=sc.classId and st.clazz=#{classId}")
    public int countyfb(String classId);

    //查询招生表信息
    @Select("select * from enrollment where reviewStatus=2 limit #{startIndex},#{pageSize}")
    public List<Enrollment> list123(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
    @Select("select * from enrollment where reviewStatus=2")
    public List<Enrollment> mentlist();
    @Select("select count(*) from enrollment where reviewStatus=2")
    public int count123();

    //查询要分班的人
    @Select({"<script>"
            +"select * from enrollment where enrollmentId in "
            +"<foreach collection='array' item='eid' open='(' separator=',' close=')'>#{eid}</foreach>"
            +"</script>"})
    public List<Enrollment> wfb(Integer [] eid);

    //分班
    @Insert("<script>"
            +"insert into student(password,stuname,middleschool,sex,cardid,phone,clazz,tuixue,stat,huor) values"
            +"<foreach collection='estu' item='st' separator=','>"
            +"('123456',#{st.studName},#{st.school},#{st.sex},#{st.card},#{st.tell},#{clazz},'否',1,0)"
            +"</foreach>"
            +"</script>")
    boolean classfb(@Param("estu") List<Enrollment> estu,@Param("clazz") Integer clazz);

    //分班之后删除
    @Delete({"<script>",
            "delete from enrollment where enrollmentId in",
            "<foreach collection='array' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
            "</script>"})
    boolean deletefb(Integer[] ids);

    //课程类别----------------------------------------------------------------

    //去查看课程类别
    @Select("select * from courseType where 1=1 limit #{startIndex},#{pageSize}")
    public List<Coursetype> lookctype(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);

    //总行数
    @Select("select count(*) from courseType")
    public int countctype();

    //新增课程类别
    @Insert("insert into courseType values(null,#{courseTypeName},#{remark})")
    public boolean addctype(Coursetype coursetype);

    //根据id查类别
    @Select("select * from coursetype where courseTypeId=#{courseTypeId}")
    public Coursetype ctypebyid(String courseTypeId);

    //修改类别
    @Update("update coursetype set courseTypeName=#{courseTypeName},remark=#{remark} where courseTypeId=#{courseTypeId}")
    public boolean updctype(Coursetype coursetype);

    //删除
    @Delete("delete from coursetype where courseTypeId=#{courseTypeId}")
    public boolean delctype(String courseTypeId);

    //课程管理---------

    //查询课程类别
    @Select("select c.*,ct.courseTypeName from course c,courseType ct where c.courseTypeId=ct.courseTypeId limit #{startIndex},#{pageSize}")
    public List<Map> lookcourse(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);

    //总行数
    @Select("select count(*) from course c,courseType ct where c.courseTypeId=ct.courseTypeId")
    public int countcourse();

    //查询类别表
    @Select("select * from coursetype")
    public List<Coursetype> coursetypelist();

    //新增课程类别
    @Insert("insert into course values(null,#{courseName},#{isobligatory},#{courseTypeId},#{remark})")
    public boolean addcourse(Course course);

    //根据id查类别
    @Select("select * from course where courseId=#{courseId}")
    public Course coursebyid(String courseId);

    //修改类别
    @Update("update course set courseName=#{courseName},isobligatory=#{isobligatory},courseTypeId=#{courseTypeId},remark=#{remark} where courseId=#{courseId}")
    public boolean updcourse(Course course);

    //删除
    @Delete("delete from course where courseId=#{courseId}")
    public boolean delcourse(String courseId);


    //试讲培训------------------------------------------------------------------

    //查询试讲表
    @Select("select t.*,c.courseName,e.empName from trial t,course c,emp e where t.courseId=c.courseId and t.empId=e.empId limit #{startIndex},#{pageSize}")
    public List<Map> looktrial(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);

    //总行数
    @Select("select count(*) from trial t,course c,emp e  where t.courseId=c.courseId and t.empId=e.empId")
    public int counttrial();

    //查询课程表
    @Select("select * from course")
    public List<Course> courselist();

    @Select("select * from emp")
    public List<Emp> elist();

    //新增
    @Insert("insert into trial values(null,#{date},#{time},#{courseId},#{type},#{empId},#{remark})")
    public boolean addtrial(Trial trial);

    //根据id查
    @Select("select * from trial where trialId=#{trialId}")
    public Trial trialbyid(String trialId);

    //修改
    @Update("update trial set date=#{date},time=#{time},courseId=#{courseId},type=#{type},empId=#{empId},remark=#{remark} where trialId=#{trialId}")
    public boolean updtrial(Trial trial);

    //删除
    @Delete("delete from trial where trialId=#{trialId}")
    public boolean deltrial(String trialId);


    //值班管理----------------------------------------------------------------------------------
    //查询
    @Select("select w.*,e.empName from weekarrange w,emp e where w.empId=e.empId order by orderId limit #{startIndex},#{pageSize}")
    public List<Map> weeklist(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
//
    //总行数
    @Select("select count(*) from weekarrange w,emp e where w.empId=e.empId order by orderId")
    public int countweek();
//
    //新增
    @Insert("insert into weekarrange values(null,#{weekArrangeName},#{ranges},#{week},#{empId},#{orderId},#{duty},#{startTime},#{endTime},#{remark})")
    public boolean addweek(Weekarrange weekarrange);
//
    //根据id查
    @Select("select * from weekarrange where weekArrangeId=#{weekArrangeId}")
    public Weekarrange weekbyid(String weekArrangeId);
////
    //修改
    @Update("update weekarrange set weekArrangeName=#{weekArrangeName},ranges=#{ranges},week=#{week},empId=#{empId},orderId=#{orderId},duty=#{duty},startTime=#{startTime},endTime=#{endTime},remark=#{remark} where weekArrangeId=#{weekArrangeId}")
    public boolean updweek(Weekarrange weekarrange);
//
    //删除
    @Delete("delete from weekarrange where weekArrangeId=#{weekArrangeId}")
    public boolean delweek(String weekArrangeId);


    //宿舍分配

    //查询宿舍 下拉框
    @Select("select * from studenthuor")
    public List<Studenthuor> stuh();
    //表格
    @Select("select sh.*,sf.floorName from studenthuor sh,studentfloor sf where sh.floorId=sf.floorId limit #{startIndex},#{pageSize}")
    public List<Map> hlist(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
    @Select("select count(*) from studenthuor sh,studentfloor sf where sh.floorId=sf.floorId")
    public int counth();

    //查询未分宿舍查学生
    @Select("select * from student s,studentclass sc  where huor='' and stat=1 and s.clazz=sc.classId  limit #{startIndex},#{pageSize}")
    public List<Student> studentlist(@Param("startIndex") int startIndex, @Param("pageSize") int pageSize);
    @Select("select count(*) from student s,studentclass sc where huor='' and stat=1 and s.clazz=sc.classId")
    public int countstu();

    //查看当前宿舍床位
    @Select("select (s.numberBeds-s.count) kon from studenthuor s where s.huorId=#{huorId} ")
    public int cwhuor(Integer huorId);
    //查看当前宿舍人数
    @Select("select s.count ren from studenthuor s where s.huorId=#{huorId} ")
    public int cwhuorr(Integer huorId);

    //修改宿舍人数
    @Update("update studenthuor set count=#{shu} where huorId=#{huorId} ")
    boolean upshuse(@Param("huorId") Integer huorId,@Param("shu") int shu);

    //分配宿舍
    @Update("<script>"
            +"<foreach collection='ids' item='st' separator=';'>"
            +"update student "
            +"<set>"
            +"huor=#{huorId}"
            +"</set>"
            +"<where>"
            +"stuId=#{st}"
            +"</where>"
            +"</foreach>"
            +"</script>")
    boolean fpstuss(@Param("ids") Integer[] ids,@Param("huorId") Integer huorId);


}
